from fastapi import APIRouter, Depends, HTTPException
from typing import Dict, Any
from ..models.models import StudentCreate, CourseCreate, ClassCreate, StudentClassCreate
from ..utils.auth import get_current_user
from ..utils.db import execute_query, execute_update
from ..utils.response import APIResponse

router = APIRouter(prefix="/admin", tags=["admin"])

async def admin_only(user: Dict[str, Any] = Depends(get_current_user)):
    if user["role"] != 3:
        raise HTTPException(status_code=403, detail="仅管理员可访问")
    return user

@router.get("/students")
async def get_students(_: Dict[str, Any] = Depends(admin_only)):
    try:
        students = execute_query("SELECT StudentID, Name, Major, RoleID FROM Student")
        return APIResponse.success(students, "获取成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.post("/students")
async def create_student(student: StudentCreate, _: Dict[str, Any] = Depends(admin_only)):
    try:
        # 检查学号是否已存在
        existing = execute_query("SELECT StudentID FROM Student WHERE StudentID = %s", (student.student_id,))
        if existing:
            return APIResponse.error(f"学号 {student.student_id} 已存在", 400)
            
        execute_update(
            "INSERT INTO Student (StudentID, Name, Major, Password, RoleID) VALUES (%s, %s, %s, %s, 1)",
            (student.student_id, student.name, student.major, student.password)
        )
        return APIResponse.success({}, "添加成功")
    except Exception as e:
        error_msg = str(e)
        if "Duplicate entry" in error_msg:
            return APIResponse.error("学号已存在", 400)
        return APIResponse.error(f"添加失败: {error_msg}", 500)

@router.delete("/students/{student_id}")
async def delete_student(student_id: str, _: Dict[str, Any] = Depends(admin_only)):
    try:
        execute_update("DELETE FROM Evaluation WHERE StudentID = %s", (student_id,))
        execute_update("DELETE FROM StudentClass WHERE StudentID = %s", (student_id,))
        execute_update("DELETE FROM Student WHERE StudentID = %s", (student_id,))
        return APIResponse.success({}, "删除成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.get("/courses")
async def get_courses(_: Dict[str, Any] = Depends(admin_only)):
    try:
        courses = execute_query("SELECT CourseID, Name, Credit FROM Course")
        return APIResponse.success(courses, "获取成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.post("/courses")
async def create_course(course: CourseCreate, _: Dict[str, Any] = Depends(admin_only)):
    try:
        # 检查课程编号是否已存在
        existing = execute_query("SELECT CourseID FROM Course WHERE CourseID = %s", (course.course_id,))
        if existing:
            return APIResponse.error(f"课程编号 {course.course_id} 已存在", 400)
            
        execute_update(
            "INSERT INTO Course (CourseID, Name, Credit) VALUES (%s, %s, %s)",
            (course.course_id, course.name, course.credit)
        )
        return APIResponse.success({}, "添加成功")
    except Exception as e:
        error_msg = str(e)
        if "Duplicate entry" in error_msg:
            return APIResponse.error("课程编号已存在", 400)
        return APIResponse.error(f"添加失败: {error_msg}", 500)

@router.delete("/courses/{course_id}")
async def delete_course(course_id: str, _: Dict[str, Any] = Depends(admin_only)):
    try:
        execute_update("DELETE e FROM Evaluation e INNER JOIN Class c ON e.ClassID = c.ClassID WHERE c.CourseID = %s", (course_id,))
        execute_update("DELETE sc FROM StudentClass sc INNER JOIN Class c ON sc.ClassID = c.ClassID WHERE c.CourseID = %s", (course_id,))
        execute_update("DELETE FROM Class WHERE CourseID = %s", (course_id,))
        execute_update("DELETE FROM Course WHERE CourseID = %s", (course_id,))
        return APIResponse.success({}, "删除成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.get("/classes")
async def get_classes(_: Dict[str, Any] = Depends(admin_only)):
    try:
        query = """
        SELECT c.ClassID, co.Name AS CourseName, t.Name AS TeacherName, 
               c.Semester, c.TimeLocation,
               COUNT(e.EvaluationID) AS EvaluationCount,
               IFNULL(AVG(e.Score), 0) AS AverageScore
        FROM Class c
        JOIN Course co ON c.CourseID = co.CourseID
        JOIN Teacher t ON c.TeacherID = t.TeacherID
        LEFT JOIN Evaluation e ON c.ClassID = e.ClassID
        GROUP BY c.ClassID
        """
        classes = execute_query(query)
        return APIResponse.success(classes, "获取成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.post("/classes")
async def create_class(class_info: ClassCreate, _: Dict[str, Any] = Depends(admin_only)):
    try:
        # 检查课程是否存在
        course = execute_query("SELECT CourseID FROM Course WHERE CourseID = %s", (class_info.course_id,))
        if not course:
            return APIResponse.error(f"课程编号 {class_info.course_id} 不存在", 400)
            
        # 检查教师是否存在
        teacher = execute_query("SELECT TeacherID FROM Teacher WHERE TeacherID = %s", (class_info.teacher_id,))
        if not teacher:
            return APIResponse.error(f"教师ID {class_info.teacher_id} 不存在", 400)
            
        execute_update(
            "INSERT INTO Class (CourseID, TeacherID, Semester, TimeLocation) VALUES (%s, %s, %s, %s)",
            (class_info.course_id, class_info.teacher_id, class_info.semester, class_info.time_location)
        )
        return APIResponse.success({}, "添加成功")
    except Exception as e:
        return APIResponse.error(f"添加失败: {str(e)}", 500)

@router.post("/student-class")
async def create_student_class(student_class: StudentClassCreate, _: Dict[str, Any] = Depends(admin_only)):
    try:
        # 检查学生是否存在
        student = execute_query("SELECT StudentID FROM Student WHERE StudentID = %s", (student_class.student_id,))
        if not student:
            return APIResponse.error(f"学号 {student_class.student_id} 不存在", 400)
            
        # 检查班级是否存在
        class_info = execute_query("SELECT ClassID FROM Class WHERE ClassID = %s", (student_class.class_id,))
        if not class_info:
            return APIResponse.error(f"班级ID {student_class.class_id} 不存在，请检查班级列表", 400)
            
        # 检查是否已选课
        existing = execute_query(
            "SELECT * FROM StudentClass WHERE StudentID = %s AND ClassID = %s",
            (student_class.student_id, student_class.class_id)
        )
        if existing:
            return APIResponse.error(f"学生 {student_class.student_id} 已经选了班级 {student_class.class_id}", 400)
            
        execute_update(
            "INSERT INTO StudentClass (StudentID, ClassID, SelectTime) VALUES (%s, %s, NOW())",
            (student_class.student_id, student_class.class_id)
        )
        return APIResponse.success({}, "添加成功")
    except Exception as e:
        error_msg = str(e)
        if "Duplicate entry" in error_msg:
            return APIResponse.error("该学生已经选了这门课", 400)
        elif "foreign key constraint" in error_msg.lower():
            if "ClassID" in error_msg:
                return APIResponse.error("班级不存在，请先创建班级", 400)
            elif "StudentID" in error_msg:
                return APIResponse.error("学生不存在，请先添加学生", 400)
        return APIResponse.error(f"添加失败: {error_msg}", 500)

@router.get("/enrollments")
async def get_enrollments(_: Dict[str, Any] = Depends(admin_only)):
    try:
        query = """
        SELECT sc.StudentID, s.Name AS StudentName, 
               sc.ClassID, co.Name AS CourseName, sc.SelectTime,
               EXISTS(SELECT 1 FROM Evaluation e WHERE e.StudentID = sc.StudentID AND e.ClassID = sc.ClassID) AS HasEvaluated
        FROM StudentClass sc
        JOIN Student s ON sc.StudentID = s.StudentID
        JOIN Class c ON sc.ClassID = c.ClassID
        JOIN Course co ON c.CourseID = co.CourseID
        ORDER BY sc.SelectTime DESC
        """
        enrollments = execute_query(query)
        return APIResponse.success(enrollments, "获取成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.delete("/student-class")
async def delete_student_class(student_id: str, class_id: int, _: Dict[str, Any] = Depends(admin_only)):
    try:
        execute_update("DELETE FROM Evaluation WHERE StudentID = %s AND ClassID = %s", (student_id, class_id))
        execute_update("DELETE FROM StudentClass WHERE StudentID = %s AND ClassID = %s", (student_id, class_id))
        return APIResponse.success({}, "删除成功")
    except Exception as e:
        return APIResponse.error(str(e), 500)

@router.get("/statistics")
async def get_statistics(_: Dict[str, Any] = Depends(admin_only)):
    try:
        # 使用单个查询获取所有统计数据
        stats_query = """
        SELECT 
            (SELECT COUNT(*) FROM Student) as student_count,
            (SELECT COUNT(*) FROM Teacher WHERE RoleID = 2) as teacher_count,
            (SELECT COUNT(*) FROM Course) as course_count,
            (SELECT COUNT(*) FROM Class) as class_count,
            (SELECT COUNT(*) FROM Evaluation) as evaluation_count,
            (SELECT IFNULL(AVG(Score), 0) FROM Evaluation) as average_score
        """
        
        result = execute_query(stats_query)
        if result and len(result) > 0:
            stats = result[0]
        else:
            stats = {
                "student_count": 0,
                "teacher_count": 0,
                "course_count": 0,
                "class_count": 0,
                "evaluation_count": 0,
                "average_score": 0
            }
            
        return APIResponse.success(stats, "获取成功")
    except Exception as e:
        return APIResponse.error(str(e), 500) 